﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data;
using QD.DataBase.Connection;

namespace QD.DataBase.DBHelper
{
    public abstract class SQLHelper
    {
        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

        protected DataBaseType type;

        public virtual IDbCommand CreateCommand()
        {
            IDbCommand cmd = null;
            switch (type)
            {
                case DataBaseType.MSSql:
                    cmd = new System.Data.SqlClient.SqlCommand();
                    break;
                case DataBaseType.MySql:
                    cmd = new MySql.Data.MySqlClient.MySqlCommand();
                    break;
            }
            return cmd;
        }

        public virtual IDbConnection CreateConnection(string connectionString)
        {
            IDbConnection conn = null;

            switch (type)
            {
                case DataBaseType.MSSql:
                    conn = new System.Data.SqlClient.SqlConnection(connectionString);
                    break;
                case DataBaseType.MySql:
                    conn = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
                    break;

            }
            return conn;
        }

        public virtual IDbDataAdapter CreateAdapter(IDbCommand cmd)
        {
            IDbDataAdapter adap = null;
            switch (type)
            {
                case DataBaseType.MSSql:
                    adap = new System.Data.SqlClient.SqlDataAdapter(cmd as System.Data.SqlClient.SqlCommand);
                    break;
                case DataBaseType.MySql:
                    adap = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd as MySql.Data.MySqlClient.MySqlCommand);
                    break;

            }
            return adap;
        }

        public int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
        {
            IDbCommand cmd = this.CreateCommand();
            int count = 0;
            using (IDbConnection conn = CreateConnection(connectionString))
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);

                try
                {
                    count = cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    conn.Close();
                    throw ex;
                }
                cmd.Parameters.Clear();
            }

            return count;

        }

        public int ExecuteNonQuery(IDbConnection connection, CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
        {

            IDbCommand cmd = CreateCommand();
            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            int count = 0;
            try
            {

                count = cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {

                throw ex;
            }
            cmd.Parameters.Clear();

            return count;
        }

        public int ExecuteNonQuery(IDbTransaction trans, CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
        {

            IDbCommand cmd = CreateCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
            int count = 0;
            try
            {
                count = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {

                throw ex;
            }
            cmd.Parameters.Clear();

            return count;
        }

        public IDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
        {

            IDbCommand cmd = CreateCommand();
            IDbConnection conn = CreateConnection(connectionString);

            try
            {

                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                
                return rdr;
            }
            catch (Exception ex)
            {
                conn.Close();

                throw ex;
            }
            
        }

        public IDataReader ExecuteReader(IDbConnection connection, CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
        {

            IDbCommand cmd = CreateCommand();
            try
            {

                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                IDataReader rdr = cmd.ExecuteReader();
                return rdr;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
        {

            IDbCommand cmd = CreateCommand();

            using (IDbConnection connection = CreateConnection(connectionString))
            {

                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                object obj = null;
                try
                {
                    obj = cmd.ExecuteScalar();
                }
                catch (Exception ex)
                {

                    connection.Close();
                    throw ex;
                }
                finally
                {
                    connection.Close();
                }

                cmd.Parameters.Clear();
                return obj;
            }
        }

        public object ExecuteScalar(IDbConnection connection, CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
        {

            IDbCommand cmd = CreateCommand();
            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            object obj = null;
            try
            {
                obj = cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {

                throw ex;
            }
            cmd.Parameters.Clear();

            return obj;
        }

        public DataTable ExecuteDataTable(string connectionString, CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
        {

            IDbCommand cmd = CreateCommand();
            DataSet ds = new DataSet();
            DataTable dt = null;
            using (IDbConnection connection = CreateConnection(connectionString))
            {

                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                IDbDataAdapter adap = CreateAdapter(cmd);
                adap.Fill(ds);
                cmd.Parameters.Clear();
                dt = ds.Tables != null && ds.Tables.Count > 0 ? ds.Tables[0] : null;

                return dt;
            }

        }

        public DataSet ExecuteDataSet(string connectionString, CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
        {

            IDbCommand cmd = CreateCommand();
            DataSet ds = new DataSet();
            using (IDbConnection connection = CreateConnection(connectionString))
            {

                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                IDbDataAdapter adap = CreateAdapter(cmd);
                adap.Fill(ds);
                cmd.Parameters.Clear();

                return ds;
            }

        }

        public void ExecuteTransation(String connectionString, IsolationLevel level, TransationHandle handle)
        {

            IDbConnection conn = this.CreateConnection(connectionString);
            try
            {
                conn.Open();
                IDbTransaction trans = conn.BeginTransaction(level);
                DbTransationEvent e = new DbTransationEvent(trans);
                handle(e);
                if (!e.Cancel)
                    trans.Commit();
            }
            catch
            {
                throw;
            }
            finally
            {
                conn.Close(); conn.Dispose();

            }

        }

        public void CacheParameters(string cacheKey, params IDbDataParameter[] commandParameters)
        {
            parmCache[cacheKey] = commandParameters;
        }

        public IDbDataParameter[] GetCachedParameters(string cacheKey)
        {
            IDbDataParameter[] cachedParms = (IDbDataParameter[])parmCache[cacheKey];

            if (cachedParms == null)
                return null;

            IDbDataParameter[] clonedParms = new IDbDataParameter[cachedParms.Length];

            for (int i = 0, j = cachedParms.Length; i < j; i++)
                clonedParms[i] = (IDbDataParameter)((ICloneable)cachedParms[i]).Clone();

            return clonedParms;
        }

        private void PrepareCommand(IDbCommand cmd, IDbConnection conn, IDbTransaction trans, CommandType cmdType, string cmdText, IDbDataParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (trans != null)
                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (IDbDataParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }
    }

}
